package com.ovopark.organize.server.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import com.ovopark.organize.server.model.bo.DepartmentConfigureRelationBo;
import com.ovopark.organize.server.model.entity.DepartmentConfigure;
import com.ovopark.organize.server.oss.OssHelper;
import com.ovopark.organize.server.oss.StorageModule;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URL;
import java.net.URLEncoder;
import java.util.*;

/**
 * Office导出工具类
 */
@Slf4j
public class OfficeExportUtil {

    /**
     * 允许导出的最大条数
     */
    private static final Integer EXPORT_EXCEL_MAX_NUM = 10000;

    public static  Integer MODULE_DEPARTMENT = 0;
    public static  Integer MODULE_USER = 1;
    public static  Integer MODULE_CERTIFICATE = 2;
    public static  Integer MODULE_ENTERPRISE = 3;
    public static  Integer MODULE_LOGIN = 4;


    /**
     * 获取导出的 Workbook对象
     *
     * @param title     大标题
     * @param sheetName 页签名
     * @param object    导出实体
     * @param list      数据集合
     * @return Workbook
     */
    public static Workbook getWorkbook(String title, String sheetName, Class<?> object, List<?> list) {
        // 判断导出数据是否为空
        if (list == null) {
            list = new ArrayList<>();
        }
        // 判断导出数据数量是否超过限定值
        if (list.size() > EXPORT_EXCEL_MAX_NUM) {
            title = "导出数据行数超过:" + EXPORT_EXCEL_MAX_NUM + "条,无法导出、请添加导出条件!";
            list = new ArrayList<>();
        }
        // 获取导出参数
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        // 设置导出样式
        exportParams.setStyle(ExcelStyleUtil.class);
        // 输出Workbook流
        return ExcelExportUtil.exportExcel(exportParams, object, list);
    }

    /**
     * 获取导出的 Workbook对象
     * 自定义列
     * @param title
     * @param sheetName
     * @param colList
     * @param list
     * @return
     */
    public static Workbook getWorkbook(String title, String sheetName, List<ExcelExportEntity> colList, List<?> list,Boolean isCreateHeadRows) {
        // 获取导出参数
        ExportParams params = new ExportParams(title, sheetName, ExcelType.XSSF);
        params.setCreateHeadRows(isCreateHeadRows);
        // 设置导出样式
        params.setStyle(ExcelStyleUtil.class);
        // 输出Workbook流
        return ExcelExportUtil.exportExcel(params, colList,list);
    }

    /**
     * 多 sheet导出
     */
    public static Workbook getWorkbookMore(List<Map<String, Object>> list) {
        // 输出Workbook流
        return ExcelExportUtil.exportExcel(list,ExcelType.XSSF);
    }


    public static Workbook exportExcelMore(List<Map<String, Object>> list, ExcelType type) {
        Workbook workbook = getWorkbook(type, 0);
        Iterator var3 = list.iterator();
        while(var3.hasNext()) {
            Map<String, Object> map = (Map)var3.next();
            ExcelExportService service = new ExcelExportService();
            List<ExcelExportEntity> entityList =  (List<ExcelExportEntity>) map.get("entity");
            List<Map<String, Object>> allDate = new ArrayList<>();
            allDate.addAll((Collection<? extends Map<String, Object>>) map.get("data"));
            Collection<?> dataSet = allDate;
            if(CollectionUtil.isNotEmpty(dataSet)){
                List<Map<Object, Object>> allDateNew = new ArrayList<>();
                for (Object object:dataSet) {
                    Map<Object,Object> valueObjecNew = (Map<Object, Object>) object;
                    Map<Object,Object> valueObjecMap = (Map<Object, Object>) object;
                    try {
                        valueObjecMap.forEach((k,v)->{
                            if(v !=null &&  v.toString().length() > 30000){
                                v = v.toString().substring(0,30000)+"......";
                                valueObjecNew.put(k,v);
                            }
                        });

                    }catch (Exception e){
                    }
                    allDateNew.add(valueObjecNew);
                }

                dataSet.clear();
                dataSet = allDateNew;
            }
            ExportParams params = (ExportParams)map.get("title");
            service.createSheetForMap(workbook,params , entityList, dataSet);
        }

        return workbook;
    }




    /**
     * 获取导出的 Workbook对象
     *
     * @param path 模板路径
     * @param map  导出内容map
     * @return Workbook
     */
    public static Workbook getWorkbook(String path, Map<String, Object> map) {
        // 获取导出模板
        TemplateExportParams params = new TemplateExportParams(path);
        // 设置导出样式
        params.setStyle(ExcelStyleUtil.class);
        // 输出Workbook流
        return ExcelExportUtil.exportExcel(params, map);
    }

    /**
     * 导出Excel
     *
     * @param workbook workbook流
     * @param fileName 文件名
     * @param response 响应
     */
    public static void exportExcel(Workbook workbook, String fileName, HttpServletResponse response) {
        // 输出文件
        try (OutputStream out = response.getOutputStream()) {
            // 获取文件名并转码
            String name = URLEncoder.encode(fileName, "UTF-8")+ ".xlsx";
            // 编码
            response.setCharacterEncoding("UTF-8");
            // 下载文件的默认名称
            response.setHeader("Content-disposition", "attachment;filename="+name+";"+"filename*=utf-8''"+name);
            // 输出表格
            workbook.write(out);
        } catch (IOException e) {
            log.error("文件导出异常,详情如下:", e);
        } finally {
            try {
                if (workbook != null) {
                    // 关闭输出流
                    workbook.close();
                }
            } catch (IOException e) {
                log.error("文件导出异常,详情如下:", e);
            }
        }
    }

    public static String uploadToOss(Workbook workbook, StorageModule module) throws IOException {
        //取当前时间的长整形值包含毫秒
        long millis = System.currentTimeMillis();
        //加上三位随机数
        Random random = new Random();
        int end3 = random.nextInt(999);
        //如果不足三位前面补0
        String str = millis + String.format("%03d", end3);
        File tempFile = File.createTempFile(str, ".xlsx");
        FileOutputStream fos = new FileOutputStream(tempFile);
        workbook.write(fos);

        String fullPath = OssHelper.uploadFile(tempFile, module);

        return fullPath;
    }



    public static String uploadToOssNew(Workbook workbook,Integer moduleType,Integer groupId) throws IOException {
        //取当前时间的长整形值包含毫秒
        long millis = System.currentTimeMillis();
        //加上三位随机数
        Random random = new Random();
        int end3 = random.nextInt(999);
        //如果不足三位前面补0
        String str = millis + String.format("%03d", end3);
        File tempFile = File.createTempFile(str, ".xlsx");
        FileOutputStream fos = new FileOutputStream(tempFile);
        workbook.write(fos);

        String moduleTypeStr = moduleType( moduleType);
        String customFileName = tempFile.getName().replaceAll("\\.xlsx","").trim();
        String path = "";
        if(groupId ==null){
            path = "super"+moduleTypeStr;
        }else {
             path = groupId+moduleTypeStr;
        }
   
        String fullPath = OssHelper.uploadFileNew(tempFile,path,customFileName);
//        String fullPath = StorageUploader.upload(tempFile,path,customFileName);
        return fullPath;
    }



    public static String uploadFileToOssNew( File tempFile,Integer moduleType,Integer groupId,String suffix) throws IOException {
        //取当前时间的长整形值包含毫秒//加上三位随机数
        //如果不足三位前面补0
        String moduleTypeStr = moduleType( moduleType);
        String customFileName = tempFile.getName().replaceAll("\\.xlsx","").replaceAll("\\.zip","").trim();
        if(StringUtils.isNotEmpty(suffix)){
            customFileName = customFileName.replaceAll("\\."+suffix,"").trim();
        }
        String path = "";
        if(groupId ==null){
            path = "super"+moduleTypeStr;
        }else {
            path = groupId+moduleTypeStr;
        }
        String fullPath = OssHelper.uploadFileNew(tempFile,path,customFileName);
        return fullPath;
    }


    public static String moduleType(Integer moduleType){
        String module = "/";
        switch (moduleType){
            case 0:
                module = module+"store";
                break;
            case 1:
                module = module+ "user";
                break;
            case 2:
                module = module+"certificate";
                break;
            case 3:
                module = module+"enterprise";
                break;
            case 4:
                module = module+"loginLog";
                break;
            default:
                module = module+"other";
                break;

        }
        return module;
    }


    /**
     * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
     *
     * @author HeJD
     * @date 2018/10/9 13:54
     * @param list 导出的实体类
     * @param title 表头名称
     * @param sheetName sheet表名
     * @param pojoClass 映射的实体类
     * @param isCreateHeader 是否创建表头
     * @param fileName
     * @param response
     * @return
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 功能描述：默认导出方法
     *
     * @author HeJD
     * @date 2018/10/9 13:54
     * @param list 导出的实体集合
     * @param fileName 导出的文件名
     * @param pojoClass pojo实体
     * @param exportParams ExportParams封装实体
     * @param response
     * @return
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    /**
     * 功能描述：Excel导出
     *
     * @author HeJD
     * @date 2018/10/9   15:35
     * @param fileName 文件名称
     * @param response
     * @param workbook Excel对象
     * @return
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" +URLEncoder.encode(fileName, "UTF-8") );
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new  RuntimeException(e);
        }
    }

    public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {
        Workbook workbook = getWorkbook(type,0);
        for (Map<String, Object> map : list) {
            ExcelExportService service = new ExcelExportService();
            service.createSheetForMap(workbook, (ExportParams)map.get("title"), (List<ExcelExportEntity>)map.get("entity"), (Collection<?>)map.get("data"));
        }
        return workbook;
    }

    private static Workbook getWorkbook(ExcelType type, int size) {
        if (ExcelType.HSSF.equals(type)) {
            return new HSSFWorkbook();
        } else if (size < 100000) {
            return new XSSFWorkbook();
        } else {
            return new SXSSFWorkbook();
        }
    }





    public static String  addCellValue(String fullUrl, String fileName, String sheetName,
                                       List<DepartmentConfigureRelationBo> relations, Integer rowNum, Integer groupId,Integer moduleType) {
        String fullPath ="";
        FileOutputStream  outs = null;
        Workbook wb = null;// 用于Workbook级的操作，创建、删除Excel
        InputStream inp = null;
        InputStream oldIntFileStream = null ;
        try {
            log.info("addCellValue:"+fullUrl);
             oldIntFileStream = OssHelper.download(fullUrl);
            File tempFile = File.createTempFile(fileName, ".xlsx");
            outs = new FileOutputStream(tempFile);
            int len = 0;
            byte[] bytes = new byte[2048];
            //读取一个bytes的文件内容
            try {
                while (true) {
                    if (!((len = oldIntFileStream.read(bytes)) != -1)) break;

                    outs.write(bytes, 0, len);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

            inp = new FileInputStream(tempFile);
            wb = WorkbookFactory.create(inp);
            wb.write(outs);
            Sheet sheet = wb.getSheet(sheetName);
            if (sheet == null) {
                sheet = wb.getSheetAt(0);
            }
            Row firstRow = sheet.getRow(1);
            Row secondRow = sheet.getRow(2);

            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 12);
            font.setFontName("黑体");
            CellStyle style = wb.createCellStyle();
            style.setFont(font);//增加字体样式
            style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//增加垂直居中样式

            CellStyle endStyle = wb.createCellStyle();
            Font endfont = wb.createFont();
            endfont.setFontHeightInPoints((short) 11);
            endfont.setFontName("Heiti SC Light");
            endStyle.setFont(font);//增加字体样式
            endStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
            endStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            endStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            endStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            endStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            endStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            endStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);//增加垂直居中样式


            for (DepartmentConfigureRelationBo relation : relations) {
                /*for (DepartmentConfigure field : relation.getFields()) {
                    rowNum++;
                    firstRow.createCell(rowNum).setCellValue(field.getFieldName());
                }*/

//                rowNum++;
                // 分组为空，字段占上下两格
                if (relation.getCategory() == null) {
                    for (DepartmentConfigure field : relation.getFields()) {
                        rowNum++;
                        Cell createCe =  firstRow.createCell(rowNum);
                       // style.setAlignment(CellStyle.ALIGN_CENTER);//增加水平居中样式

                        createCe.setCellStyle(style);
                        int length = field.getFieldName().getBytes("GBK").length;
                        // 没分组，上下两行合并
                        //sheet.addMergedRegionUnsafe(new CellRangeAddress(1,2, rowNum, rowNum));
                        Cell createE =  secondRow.createCell(rowNum);
                        createE.setCellValue(field.getFieldName());
                      //  style.setAlignment(CellStyle.ALIGN_CENTER);//增加水平居中样式
                        createE.setCellStyle(endStyle);
                        sheet.setColumnWidth(rowNum, length * 500);
                    }
//                    Cell cell = firstRow.getCell(rowNum);
//                    cell.setCellType(Cell.CELL_TYPE_STRING);
                } else if (CollectionUtil.isNotEmpty(relation.getFields())) {
                      Integer startCategory =  rowNum+1;
                    Cell createFist = firstRow.createCell(startCategory);
                    createFist.setCellValue(relation.getCategory().getName());
                  //  style.setAlignment(CellStyle.ALIGN_CENTER);//增加水平居中样式
                    createFist.setCellStyle(style);
                    // 分组单元格合并
                    Integer endCategory = rowNum+ relation.getFields().size();
                    Integer num = endCategory - startCategory;

                    if(num>0){
                       // sheet.setColumnWidth(startCategory, 25);
                        Cell createEnd = firstRow.createCell(endCategory);
                        createEnd.setCellStyle(style);
                        sheet.addMergedRegionUnsafe(new CellRangeAddress(1,1, startCategory, endCategory));
                        int lengthCa = relation.getCategory().getName().getBytes("GBK").length;
                        sheet.setColumnWidth(startCategory, lengthCa * 500);
                    }
                 //   style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
                    //sheet.setDefaultColumnStyle(rowNum + 1,style);
                    for (DepartmentConfigure field : relation.getFields()) {
                        rowNum++;
                        Cell secondFist =  secondRow.createCell(rowNum);
                        secondFist.setCellValue(field.getFieldName());
                        secondFist.setCellStyle(endStyle);
                        int length = field.getFieldName().getBytes("GBK").length;
                        sheet.setColumnWidth(rowNum, length * 500);
                        log.info("field=="+field.getFieldName());

                    }
                } else {
                    // 只有分组没有字段，不导出
                    // 和上一条相同分组，合并
//                    secondRow.createCell(rowNum).setCellValue(departmentConfigure.getFieldName());
                }
                //设置列 字段类型  都设置为 文本类型


            }
            outs.close();
            //  fullPath = OfficeExportUtil.uploadToOss(wb,  StorageModule.OVOPARK);
            fullPath = OfficeExportUtil.uploadToOssNew(wb,moduleType,groupId);
            // 本地文件 删
        } catch (Exception e) {
            e.printStackTrace();
            log.info("文件错误:{}",groupId);
        } finally {
            try {
                outs.close();
                wb.close();
                inp.close();
                oldIntFileStream.close();
            } catch (IOException e) {
                e.printStackTrace();
                log.info("文件流关闭错误:{}",groupId);
            }
        }
     return fullPath;

    }

    /**
     * rgb转int
     */
    private static int getIntFromColor(int Red, int Green, int Blue){
        Red = (Red << 16) & 0x00FF0000;
        Green = (Green << 8) & 0x0000FF00;
        Blue = Blue & 0x000000FF;
        return 0xFF000000 | Red | Green | Blue;
    }

    /**
     * int转byte[]
     */
    public static byte[] intToByteArray(int i) {
        byte[] result = new byte[4];
        result[0] = (byte)((i >> 24) & 0xFF);
        result[1] = (byte)((i >> 16) & 0xFF);
        result[2] = (byte)((i >> 8) & 0xFF);
        result[3] = (byte)(i & 0xFF);
        return result;
    }


    public static String uploadToOss(String filePath) {

        //取当前时间的长整形值包含毫秒
        long millis = System.currentTimeMillis();
        //加上三位随机数
        Random random = new Random();
        int end3 = random.nextInt(999);
        //如果不足三位前面补0
        String str = millis + String.format("%03d", end3);
          log.info("filePath");
        Integer index = filePath.lastIndexOf("/");
        String fileName = str+filePath.substring(index,filePath.length());
        File tempFile = new File(filePath);
        String fullPath = OssHelper.uploadFile1(tempFile,fileName, StorageModule.OVOPARK);

        return fullPath;
    }


    public static  File  downLoadFile(String fullUrl,String fieldName) throws IOException {
        //File tempFile = new File(fullUrl);
      /*  File excelFile = null;
        InputStream inputStream = OssHelper.download(fullUrl);
        if(inputStream !=null){
            try {
                excelFile = File.createTempFile(fieldName, ".xlsx");
                FileOutputStream  outs = new FileOutputStream(excelFile);
                int len = 0;
                byte[] bytes = new byte[2048];
                //读取一个bytes的文件内容
                try {
                    while (true) {
                        if (!((len = inputStream.read(bytes)) != -1)) break;

                        outs.write(bytes, 0, len);
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }

                InputStream inp = new FileInputStream(excelFile);
                inp.close();
                inputStream.close();


            }catch (Exception e){

            }

        }*/







            File outputFile = new File(fieldName);
            URL url = new URL(fullUrl);
            BufferedInputStream bis = new BufferedInputStream(url.openStream());
            FileOutputStream fis = new FileOutputStream(outputFile);
            byte[] buffer = new byte[1024];
            int count=0;
            while((count = bis.read(buffer,0,1024)) != -1)
            {
                fis.write(buffer, 0, count);
            }
            fis.close();
            bis.close();


        return outputFile;
    }




}
